﻿using System;
using System.Data;
using System.Text;
using System.Collections.Generic;

using MySql.Data.MySqlClient;
using Think.Lenovo.Com.Models.Users;
using Think.Lenovo.Com.Common;

namespace Think.Lenovo.Com.DataAccess.User
{
    public class UserInfo
    {
        /// <summary>
        /// 添加用户信息
        /// </summary>
        /// <param name="model">用户信息实体类</param>
        /// <returns>自增id</returns>
        public long AddUserInfo(UserInfoModel model)
        {
            #region //添加用户信息

            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;

            #region//sql语句
            StringBuilder sql = new StringBuilder(128);
            sql.Append("insert into ")
                .Append("tbl_userinfo")
                .Append("(userId, realName, companyAddress, companyEmail, ")
                .Append("companyTel, userPhoneNumber, companyRegion, formatedUserName,")
                .Append(" companyGrade, companyRegisteDate, companyType, companyProperty,")
                .Append("companyPostalCode, forward, userEmail, companyFax, ")
                .Append("companyBusinessNumber, companyRegisteFund, companyName,")
                .Append("companyProvinceId,companyCityId)")
                .Append("values")

                .Append("(@userId, @realName, @companyAddress, @companyEmail, ")
                .Append("@companyTel, @userPhoneNumber, @companyRegion, @formatedUserName, ")
                .Append("@companyGrade, @companyRegisteDate, @companyType, @companyProperty,")
                .Append("@companyPostalCode, @forward, @userEmail, @companyFax, ")
                .Append("@companyBusinessNumber, @companyRegisteFund,@companyName,@companyProvinceId,@companyCityId)");

            #endregion

            #region//Sql参数数据
            MySqlParameter[] ps = new MySqlParameter[] {
                    new MySqlParameter("@userId",MySqlDbType.Int32),
                    new MySqlParameter("@realName",MySqlDbType.VarChar,50),
                    new MySqlParameter("@companyAddress",MySqlDbType.VarChar,128),
                    new MySqlParameter("@companyEmail",MySqlDbType.VarChar,128),
                    new MySqlParameter("@companyTel",MySqlDbType.VarChar,15),
                    new MySqlParameter("@userPhoneNumber",MySqlDbType.VarChar,15),
                    new MySqlParameter("@companyRegion",MySqlDbType.Int32),
                    new MySqlParameter("@formatedUserName",MySqlDbType.VarChar,20),
                    new MySqlParameter("@companyGrade",MySqlDbType.Int32),
                    new MySqlParameter("@companyRegisteDate",MySqlDbType.DateTime),
                    new MySqlParameter("@companyType",MySqlDbType.Int32),
                    new MySqlParameter("@companyProperty",MySqlDbType.Int32),
                    new MySqlParameter("@companyPostalCode",MySqlDbType.VarChar,6),
                    new MySqlParameter("@forward",MySqlDbType.Text),
                    new MySqlParameter("@userEmail",MySqlDbType.VarChar,128),
                    new MySqlParameter("@companyFax",MySqlDbType.VarChar,15),
                    new MySqlParameter("@companyBusinessNumber",MySqlDbType.VarChar,50),
                    new MySqlParameter("@companyRegisteFund",MySqlDbType.Decimal),
                    new MySqlParameter("@companyName",MySqlDbType.VarChar,100),
                    new MySqlParameter("@companyProvinceId",MySqlDbType.Int32),
                    new MySqlParameter("@companyCityId",MySqlDbType.Int32)
                };

            ps[0].Value = model.UserId;
            ps[1].Value = model.RealName;
            ps[2].Value = model.CompanyAddress;
            ps[3].Value = model.CompanyEmail;
            ps[4].Value = model.CompanyTel;
            ps[5].Value = model.UserPhoneNumber;
            ps[6].Value = model.CompanyRegion;
            ps[7].Value = model.FormatedUserName;
            ps[8].Value = model.CompanyGrade;
            ps[9].Value = model.CompanyRegisteDate;
            ps[10].Value = model.CompanyType;
            ps[11].Value = model.CompanyProperty;
            ps[12].Value = model.CompanyPostalCode;
            ps[13].Value = model.Forward;
            ps[14].Value = model.UserEmail;
            ps[15].Value = model.CompanyFax;
            ps[16].Value = model.CompanyBusinessNumber;
            ps[17].Value = model.CompanyRegisteFund;
            ps[18].Value = model.CompanyName;
            ps[19].Value = model.CompanyProvinceId;
            ps[20].Value = model.CompanyCityId;
            #endregion
            long id = MysqlHelper.Insert(conn, sql.ToString(), ps);
            return id;

            #endregion
        }

        /// <summary>
        /// 修改用户信息
        /// </summary>
        /// <param name="model">用户实体类</param>
        /// <returns>受影响的行数</returns>
        public int UpdateUserInfo(UserInfoModel model)
        {
            #region //修改用户信息

            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;

            #region//sql语句
            StringBuilder sql = new StringBuilder(128);
            sql.Append(" Update ")
                .Append(" tbl_userinfo ")
                .Append(" Set userId=@userId,")
                .Append(" realName=@realName,")
                .Append("companyAddress=@companyAddress,")
                .Append("companyEmail=@companyEmail, ")
                .Append("companyTel=@companyTel, ")
                .Append("userPhoneNumber=@userPhoneNumber,")
                .Append("companyRegion=@companyRegion,")
                .Append("formatedUserName=@formatedUserName,")
                .Append(" companyGrade=@companyGrade, ")
                .Append("companyRegisteDate= @companyRegisteDate,")
                .Append("companyType=@companyType, ")
                .Append("companyProperty=@companyProperty,")
                .Append("companyPostalCode=@companyPostalCode, ")
                .Append("forward=@forward,")
                .Append("userEmail=@userEmail, ")
                .Append("companyFax=@companyFax, ")
                .Append("companyBusinessNumber=@companyBusinessNumber, ")
                .Append("companyRegisteFund=@companyRegisteFund, ")
                .Append("companyName=@companyName, ")
                .Append("companyProvinceId=@companyProvinceId,")
                .Append("companyCityId=@companyCityId")
                .Append(" Where (Id=@Id or userId=@userId) ");

            #endregion

            #region//Sql参数数据
            MySqlParameter[] ps = new MySqlParameter[] {
                    new MySqlParameter("@userId",MySqlDbType.Int32),
                    new MySqlParameter("@realName",MySqlDbType.VarChar,50),
                    new MySqlParameter("@companyAddress",MySqlDbType.VarChar,128),
                    new MySqlParameter("@companyEmail",MySqlDbType.VarChar,128),
                    new MySqlParameter("@companyTel",MySqlDbType.VarChar,15),
                    new MySqlParameter("@userPhoneNumber",MySqlDbType.VarChar,15),
                    new MySqlParameter("@companyRegion",MySqlDbType.Int32),
                    new MySqlParameter("@formatedUserName",MySqlDbType.VarChar,20),
                    new MySqlParameter("@companyGrade",MySqlDbType.Int32),
                    new MySqlParameter("@companyRegisteDate",MySqlDbType.DateTime),
                    new MySqlParameter("@companyType",MySqlDbType.Int32),
                    new MySqlParameter("@companyProperty",MySqlDbType.Int32),
                    new MySqlParameter("@companyPostalCode",MySqlDbType.VarChar,6),
                    new MySqlParameter("@forward",MySqlDbType.Text),
                    new MySqlParameter("@userEmail",MySqlDbType.VarChar,128),
                    new MySqlParameter("@companyFax",MySqlDbType.VarChar,15),
                    new MySqlParameter("@companyBusinessNumber",MySqlDbType.VarChar,50),
                    new MySqlParameter("@companyRegisteFund",MySqlDbType.Decimal),
                    new MySqlParameter("@companyName",MySqlDbType.VarChar,100),
                    new MySqlParameter("@companyProvinceId",MySqlDbType.Int32),
                    new MySqlParameter("@companyCityId",MySqlDbType.Int32),
                    new MySqlParameter("@Id",MySqlDbType.Int32)
                };

            ps[0].Value = model.UserId;
            ps[1].Value = model.RealName;
            ps[2].Value = model.CompanyAddress;
            ps[3].Value = model.CompanyEmail;
            ps[4].Value = model.CompanyTel;
            ps[5].Value = model.UserPhoneNumber;
            ps[6].Value = model.CompanyRegion;
            ps[7].Value = model.FormatedUserName;
            ps[8].Value = model.CompanyGrade;
            ps[9].Value = model.CompanyRegisteDate;
            ps[10].Value = model.CompanyType;
            ps[11].Value = model.CompanyProperty;
            ps[12].Value = model.CompanyPostalCode;
            ps[13].Value = model.Forward;
            ps[14].Value = model.UserEmail;
            ps[15].Value = model.CompanyFax;
            ps[16].Value = model.CompanyBusinessNumber;
            ps[17].Value = model.CompanyRegisteFund;
            ps[18].Value = model.CompanyName;
            ps[19].Value = model.CompanyProvinceId;
            ps[20].Value = model.CompanyCityId;
            ps[21].Value = model.Id;
            #endregion
            int affect = MysqlHelper.Update(conn, sql.ToString(), ps);
            return affect;

            #endregion
        }

        /// <summary>
        /// 更新指定用户的指定信息
        /// </summary>
        /// <param name="userId">用户id</param>
        /// <param name="kv">字段名和字段值的集合</param>
        /// <returns>受影响的行数</returns>
        public long UpdateUserInfo(int userId, Dictionary<String, Object> kv)
        {
            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;
            //Sql语句
            StringBuilder sql = new StringBuilder(256);
            sql.Append("Update  tbl_userinfo set ");
            foreach (KeyValuePair<String, Object> item in kv)
            {
                sql.Append(item.Key + "=@" + item.Key);
            }

            MySqlParameter[] ps = new MySqlParameter[kv.Count];
            {
                int i = 0;
                foreach (KeyValuePair<String, Object> item in kv)
                {
                    ps[i++] = new MySqlParameter("@" + item.Key, item.Value);
                }
            }
            return MysqlHelper.Update(conn, sql.ToString(), ps);
        }

        /// <summary>
        /// 根据一个用户id 得到他的信息
        /// </summary>
        /// <param name="userId">用户Id</param>
        /// <returns>用户信息实体</returns>
        public UserInfoModel GetUserInfo(int userId)
        {
            UserInfoModel model = new UserInfoModel();
            #region //

            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;

            #region//sql语句
            StringBuilder sql = new StringBuilder(128);
            sql.Append(" Select * From ")
                .Append(" tbl_userinfo ")
                .Append(" Where userId=@userId");
            #endregion

            #region//Sql参数数据
            MySqlParameter[] ps = new MySqlParameter[] {
                    new MySqlParameter("@userId",MySqlDbType.Int32)
                };
            ps[0].Value = userId;
            #endregion

            DataSet ds = MysqlHelper.Query(conn, sql.ToString(), ps);
            if (ds != null)
            {
                if (ds.Tables[0] != null)
                {
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        model.Id = Convert.ToInt32(ds.Tables[0].Rows[0]["Id"]);
                        model.UserId = Convert.ToInt32(ds.Tables[0].Rows[0]["userId"]);
                        model.RealName = Convert.ToString(ds.Tables[0].Rows[0]["realName"]);
                        model.CompanyAddress = Convert.ToString(ds.Tables[0].Rows[0]["companyAddress"]);
                        model.CompanyEmail = Convert.ToString(ds.Tables[0].Rows[0]["companyEmail"]);
                        model.CompanyTel = Convert.ToString(ds.Tables[0].Rows[0]["companyTel"]);
                        model.UserPhoneNumber = Convert.ToString(ds.Tables[0].Rows[0]["userPhoneNumber"]);
                        model.CompanyRegion = Convert.ToInt32(ds.Tables[0].Rows[0]["companyRegion"]);
                        model.FormatedUserName = Convert.ToString(ds.Tables[0].Rows[0]["formatedUserName"]);
                        model.CompanyGrade = Convert.ToInt32(ds.Tables[0].Rows[0]["companyGrade"]);
                        model.CompanyRegisteDate = Convert.ToDateTime(ds.Tables[0].Rows[0]["companyRegisteDate"]);
                        model.CompanyType = Convert.ToInt32(ds.Tables[0].Rows[0]["companyType"]);
                        model.CompanyProperty = Convert.ToInt32(ds.Tables[0].Rows[0]["companyProperty"]);
                        model.CompanyPostalCode = Convert.ToString(ds.Tables[0].Rows[0]["companyPostalCode"]);
                        model.Forward = Convert.ToString(ds.Tables[0].Rows[0]["forward"]);
                        model.UserEmail = Convert.ToString(ds.Tables[0].Rows[0]["userEmail"]);
                        model.CompanyFax = Convert.ToString(ds.Tables[0].Rows[0]["companyFax"]);
                        model.CompanyBusinessNumber = Convert.ToString(ds.Tables[0].Rows[0]["companyBusinessNumber"]);
                        model.CompanyRegisteFund = Convert.ToDecimal(ds.Tables[0].Rows[0]["companyRegisteFund"]);
                        model.CompanyName = Convert.ToString(ds.Tables[0].Rows[0]["companyName"]);
                        model.CompanyProvinceId = Convert.ToInt32(ds.Tables[0].Rows[0]["companyProvinceId"]);
                        model.CompanyCityId = Convert.ToInt32(ds.Tables[0].Rows[0]["companyCityId"]);
                    }
                }
            }
            return model;
            #endregion
        }

        /// <summary>
        /// 取出对应的分区管理员id
        /// </summary>
        /// <param name="level"></param>
        /// <returns></returns>
        public DataSet GetUserIdByRegionLevel(int level, int region)
        {
            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select tbl_user.id from tbl_user ,tbl_userinfo where tbl_user.Id= tbl_userinfo.userId and tbl_user.userRole=" + level + " and tbl_userinfo.companyRegion =" + region); ;
            return MysqlHelper.Query(conn, strSql.ToString());
        }

        /// <summary>
        /// 取出对应的分区管理员id
        /// </summary>
        /// <param name="level"></param>
        /// <returns></returns>
        public int GetUserIdByRegionLevelCount(int level, int region)
        {
            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select Count(*) from tbl_user ,tbl_userinfo where tbl_user.Id= tbl_userinfo.userId and tbl_user.userRole=" + level + " and tbl_userinfo.companyRegion =" + region); ;
            return Int32.Parse(MysqlHelper.ExecuteScalar(conn, strSql.ToString()).ToString());
        }
        /// <summary>
        /// 给指定用户 加integral分,integral必须大于零
        /// </summary>
        /// <param name="userId">用户id</param>
        /// <param name="integral">分值</param>
        /// <returns>成功:true 否则false</returns>
        public bool AddIntegral(int userId, int integral)
        {
            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;
            if (integral < 1) {
                return false;
            }
            //Sql语句
            StringBuilder sql = new StringBuilder(256);
            sql.Append("Update  tbl_userinfo set ")
                .Append("companyGrade = companyGrade+" + integral);
            int affected = MysqlHelper.Update(conn, sql.ToString(), null);
            if (affected > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 给指定用户减去integral分值
        /// </summary>
        /// <param name="userId">用户id</param>
        /// <param name="integral">分值</param>
        /// <returns>成功 true 否则false</returns>
        public bool SubIntegral(int userId, int integral)
        {
            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;
            if (integral < 1)
            {
                return false;
            }
            //Sql语句
            StringBuilder sql = new StringBuilder(256);
            sql.Append("Update  tbl_userinfo set ")
                .Append("companyGrade = companyGrade-" + integral);
            int affected = MysqlHelper.Update(conn, sql.ToString(), null);
            if (affected > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
    }
}
